<?php

session_start();
/*require_once '../include/config.lib.php';
require_once '../include/database.lib.php';
*/

function getPaperTags($paperId) {	
	ConnectToDB();
	// Load tags about the paper.
	//$tagReq = DBExecute('SELECT label FROM tagging AS t, resource AS r WHERE t.resourceid=r.id AND r.bibtexid=?', $paperId);
	$tagReq = DBExecute('SELECT label,num FROM tagcount AS t, resource AS r WHERE t.resourceid=r.id AND r.bibtexid=?', $paperId);
	$tags = array();
	
	// currently we have taggings that don't have associated tagcount (tagcount is created after some rows are added to tagging)
	//this part gets tags and their frequency, if any tag is assigned after creation of the tagcount table 
	$tagcountFilled = false;
	while ($row = $tagReq->fetchAssocRow()) {
		$tagcountFilled = true;
		$tags[trim($row['LABEL'])] = $row['NUM'];
	}
	// if this resource has no tags in tagcount table, we look into tagging
	// TODO, after clearing database from inconsistency between tagging and tagcount, this should be eliminated
	if( !$tagcountFilled ){
		$tagReq = DBExecute('SELECT distinct(label) FROM tagging AS t, resource AS r WHERE t.resourceid=r.id AND r.bibtexid=?', $paperId);
		while ($row = $tagReq->fetchAssocRow()) {
		// TODO: there should be frequency of the tag appearances.
		$tags[trim($row['LABEL'])] = rand(1, 4);
		}
	}
	arsort($tags);
	return $tags;
}

function getPaperKeywords($paperId){
	// Load keywords:
	ConnectToDB();
	$keywordsReq = DBExecute('SELECT distinct(label) FROM keywords WHERE bibtexid=?', $paperId);
	$keywords = '';
	while ( $keyrow = $keywordsReq->fetchAssocRow()) {
		$keywords .= $keyrow['LABEL'] . '; ';
	} 
	return $keywords;
}

function getTagsOfReadPapers($personId){
	ConnectToDB();	
	/*$tagsReq = DBExecute('SELECT tc.label, SUM(tc.num) FROM ( 
							(SELECT label, t.resourceid as tid FROM DB2INST1.Annotation a, DB2INST1.Tagging t, DB2INST1.Resource r, DB2INST1.Resource rr 
								WHERE a.personid = ? AND a.resourceid = r.id AND r.bibtexid = rr.bibtexid AND rr.id = t.resourceid) 
							UNION 
							(SELECT label, t.resourceid as tid FROM DB2INST1.Reading read, DB2INST1.Tagging t, DB2INST1.Resource r, DB2INST1.Resource rr 
								WHERE read.personid = ? AND read.resourceid = r.id AND r.bibtexid = rr.bibtexid AND rr.ID = t.resourceid)
							) as tagss, TagCount tc 
							WHERE tagss.label = tc.label AND tagss.tid = tc.resourceid 
							GROUP BY (tc.label, tc.resourceid)',array($personId,$personId));*/
	
	
	$tagsReq = DBExecute('SELECT label, SUM(tagss.num) FROM ( 
							(SELECT label, count(a.id) as num FROM Annotation a, Tagging t, Resource r, Resource rr 
								WHERE a.personid = ? AND a.resourceid = r.id AND r.bibtexid = rr.bibtexid AND rr.id = t.resourceid
								GROUP BY (label)) 
							UNION 
							(SELECT label, count(read.resourceid) as num FROM Reading read, Tagging t, Resource r, Resource rr 
								WHERE read.personid = ? AND read.resourceid = r.id AND r.bibtexid = rr.bibtexid AND rr.ID = t.resourceid
								GROUP BY (label))
							) as tagss 
							GROUP BY (tagss.label)',array($personId,$personId));
	$tags = array();
	while($row = $tagsReq->fetchAssocRow()){		
		$tags[trim($row['LABEL'])] = $row['NUM'];
	}
	return $tags;
}

function getTagsAssignedByUser($personId){
	ConnectToDB();		
	$tagsReq = DBExecute('SELECT label, count(*) as num FROM Tagging  
							WHERE personid = ?   
							GROUP BY label',array($personId));
	$tags = array();
	while($row = $tagsReq->fetchAssocRow()){		
		$tags[trim($row['LABEL'])] = $row['NUM'];
	}
	return $tags;
}

function papersTaggedBy($tag){
	ConnectToDB();
	$tagsReq = DBExecute('SELECT distinct(b.id) as bibtexid, title, abstract 
							FROM (							
								SELECT (b.id) 
								FROM bibtex b, resource r, tagcount t  
								WHERE (trim(t.label) LIKE ? OR trim(t.label) = ? ) AND t.resourceid = r.id 
								AND r.bibtexid = b.id 
								ORDER BY t.num DESC							
							) as bib, bibtex b where bib.id = b.id',array('__'.$tag, $tag));
	/*$papers = array();
	while($row = $tagsReq->fetchAssocRow()){		
		$papers[trim($row['LABEL'])] = $row['NUM'];
	}*/
	return $tagsReq;
}

